SQL: Advanced SQL Query optimization techniques by Andy Vickler

Author:Andy Vickler [Vickler, Andy]
Language: eng
Format: azw3, epub
Published: 2021-07-10T16:00:00+00:00

The WHERE clause also has a condition guaranteeing only one row will be returned by the join. This is most likely to be an equality condition on the primary or unique key's column/s.

AND operators must be used with these conditions, and Oracle will do a nested loops operation to execute the statement.

In the statement below, two tables, called dept and emp, are clustered on the column named deptno. The emp table’s primary key is the empno column:

SELECT *

FROM emp, dept

WHERE emp.deptno = dept.deptno

AND emp.empno = 7900;

The EXPLAIN PLAN output looks like this:

OPERATION OPTIONS OBJECT_NAME

-----------------------------------------------------

SELECT STATEMENT

NESTED LOOPS

TABLE ACCESS BY ROWID EMP

INDEX UNIQUE SCAN PK_EMP

TABLE ACCESS CLUSTER DEPT

The index enforcing the primary key is called pk_emp.

RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key

This one relies on both the following conditions being true to be available:

The WHERE clause uses all the hash cluster key's columns in the equality conditions. In the case of composite cluster keys, AND operators must be used with the equality conditions.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.